This is the accompanying notebook for all data used in the analysis of NEP 2015 You can modify this to analyze other part of the NEP 2015. This notebook is focused on the ICT component.
Author: Rick Bahague / rick (at) cp-union (dot) com. http://www.cp-union.com
Download datasets from the DBM website.
In [3]:
%matplotlib inline
import pandas as pd
from pylab import *
import mpld3
rcParams['figure.figsize'] = 12, 6
pd.set_option('display.max_rows',500)
mpld3.enable_notebook()
DATA_PATH = "../data/"
#durl = 'http://datasets.flowingdata.com/crimeRatesByState2005.csv'
#rdata = genfromtxt(durl,dtype='S8,f,f,f,f,f,f,f,i',delimiter=',')
In [4]:
#Data taken from DBM Site
auto_approp = pd.read_csv(DATA_PATH + "2015 NEP-Proposed-Automatic.csv")
auto_approp.fillna(value=0,inplace=True)
new_approp = pd.read_csv(DATA_PATH + "2015 NEP-Proposed.csv")
new_approp.fillna(value=0,inplace=True)
#rename columns for clarity
new_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'Source', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
auto_approp.columns = [u'uacs_dpt_id', u'Department', u'uacs_agy_id', u'Agency',
u'uacs_fpap_id', u'Description', u'operdiv', u'operunit', u'uacs_oper_dsc', u'fndsrc',
u'Source', u'uacs_exp_cd', u'Category', u'uacs_sobj_cd',
u'Objective', u'Amount']
#select data for analysis
auto_approp_data = auto_approp[auto_approp.Amount>0][['Department','Agency','Description','Source','Category','Objective','Amount']]
auto_approp_data['NEP'] = 'AUTO'
new_approp_data = new_approp[new_approp.Amount>0][['Department','Agency','Description','Source','Category','Objective','Amount']]
new_approp_data['NEP'] = 'NEW'
all_data = auto_approp_data.append(new_approp_data)
all_data[ [u'NEP',u'Department', u'Agency', u'Description', u'Category','Source', u'Objective', u'Amount']].to_csv('data.csv')
In [5]:
all_data['Amount'].sum()
Out[5]:
The following lists budget items that can be categorized as ICT spending. Note that there are budget items that are actually almost the same but are treated separately and given separate budgets. Example: ICT Software and Computer Software, Information and Communication Technology Equipment and ICT Machinery and Equipment
In [6]:
ICT_items = [
"Information and Communication Technology Equipment",
"Communication Equipment",
"ICT Office Supplies",
"ICT Software",
"Cloud Computing Service",
"ICT Training Expenses",
"Rents - ICT Machinery and Equipment",
"Internet Subscription Expenses",
"ICT Consultancy Services",
"Communication Networks",
"Other General Services - ICT Services",
"ICT Software Subscription",
"Cable, Satellite, Telegraph and Radio Expenses",
"ICT Machinery and Equipment",
"Computer Software",
"Website Maintenance",
"ICT Generation, Transmission and Distribution Expenses",
"ICT Research, Exploration and Development Expenses"]
In [7]:
search = 'Mobile|ICT|Software|Website|Database|Landline|Internet|Cloud|Information and Communication|Communication Equipment|Communication Network|Cable|MITHI|Internet|Computer'
ict_budget_items=all_data[all_data.Objective.str.contains(search,na=0)]
ict_item_department = ict_budget_items.groupby(['Department']).sum()
#ict_item_objectives = ict_budget_items.groupby(['Category','Objective']).sum()
ict_item_objectives = ict_budget_items.groupby(['Objective']).sum()
ict_item_objectives.sort(columns='Amount',ascending=False,inplace=True)
ict_item_department.sort(columns='Amount',ascending=False,inplace=True)
ict_item_objectives['Share %'] = ict_item_objectives['Amount']/ict_item_objectives['Amount'].sum()*100
ict_item_department['Share %'] = ict_item_department['Amount']/ict_item_department['Amount'].sum()*100
In [8]:
ict_item_objectives
Out[8]:
In [16]:
ict_item_department
Out[16]:
We also looked at programmed ICT projects in the NEP. This differ from above. Project implementation costs will include administrative costs and not only ICT services costs.
In [12]:
search_data = 'BalinkBayan Portal|Digitization|Software System|Information System|Data banking|Data Management|Knowledge Management|Information Management|Computerization|Information technology'
ict_project_details = all_data[all_data.Description.str.contains(search_data,na=0)]
a=ict_project_details[ict_project_details.NEP=='NEW'][['Department','Description','Amount']].groupby(['Department','Description']).sum()
ict_projects = a.groupby(level=0).agg(['sum','count'])
#ict_project_details.to_csv('ICT Project Details.csv')
ict_descriptions = a.groupby(level=1).agg(['sum','count'])
ict_descriptions.sort([('Amount','sum')],ascending=False,inplace=True)
ict_descriptions
Out[12]:
In [13]:
ict_budget = ict_projects.join(ict_item_department['Amount'],how='right')
ict_budget.fillna(0,inplace=True)
ict_budget.columns = ['Amount of New Project','Count of New Projects','Amount ICT Budget Items']
ict_budget.sort(columns='Amount of New Project',ascending=False,inplace=True)
The total ICT spending for NEP amounts to the following (in '000' PhP):
In [14]:
ict_budget.sum()
Out[14]:
In [17]:
ppp = [
"Internet Subscription Expenses",
"Landline","Mobile","Cable, Satellite, Telegraph and Radio Expenses"]
c=all_data[all_data.Objective.isin(ppp)]
c['Amount'].sum()/ict_budget['Amount ICT Budget Items'].sum()*100
c['Amount'].sum()
Out[17]:
In [18]:
proprietary = [
'ICT Software Subscription',
'ICT Software',
'Computer Software',"Cloud Computing Service",]
proprietary_expenses = all_data[(all_data.Objective.isin(proprietary))]
In [19]:
proprietary_expenses['Amount'].sum()/ict_budget['Amount ICT Budget Items'].sum()*100
proprietary_expenses['Amount'].sum()
Out[19]:
In [20]:
new_projects = ict_project_details[ict_project_details.NEP=='NEW']
In [37]:
benefits = [
'Basic Salary - Civilian',
'Retirement and Life Insurance Premiums','Bonus - Civilian',
'PERA - Civilian',
'Cash Gift - Civilian', 'Clothing/Uniform Allowance - Civilian',
'PhilHealth - Civilian',
'Productivity Incentive Allowance - Civilian',
'ECIP - Civilian','Pag-IBIG - Civilian',
'Honoraria - Civilian',
'Salaries and Wages - Casual/Contractual']
personnel_benefits = new_projects[new_projects.Objective.isin(benefits)]
outsourced = [
'Other General Services - ICT Services',
'Consultancy Services',
'ICT Consultancy Services',
'Cloud Computing Service','Website Maintenance']
outsourced_expenses = new_projects[new_projects.Objective.isin(outsourced)]
In [38]:
#personnel_benefits['Amount'].sum()/ict_budget['Amount of New Project'].sum()
personnel_benefits['Amount'].sum()
Out[38]:
In [39]:
#outsourced_expenses['Amount'].sum()/ict_budget['Amount of New Project'].sum()
outsourced_expenses['Amount'].sum()
Out[39]:
In [21]:
Web_Maintenance = all_data[all_data.Objective=='Website Maintenance']
temp=Web_Maintenance[['Department','Agency','Amount']].groupby(['Department','Agency']).sum()
temp.sort(columns='Amount',ascending=False,inplace=True)
temp
Out[21]:
In [22]:
#Description, Agency, Department, Category
query_str = 'Rents - ICT Machinery and Equipment'
temp = all_data[all_data.Objective==query_str].groupby(['Department','Agency','Description']).sum()
temp.sort(columns='Amount',ascending=False,inplace=True)
#temp
temp.sum()
Out[22]:
In [23]:
#Description, Agency, Department, Category
query_str = [
'Information and Communication Technology Equipment',
'Communication Equipment',
'Communication Networks',
'ICT Machinery and Equipment',
]
temp0 = all_data[all_data.Objective.isin(query_str)]
temp0['Amount'].sum()/all_data['Amount'].sum()*100
temp0.sum()
Out[23]:
In [42]: